from flask import Flask, render_template, request
import datetime
import pandas as pd
from pandas import IndexSlice

app = Flask(__name__)

# 可以手动更改, 分别对应 年月日
update_time = datetime.date(2021, 5, 8)

annual_leave = r'年假表.xlsx'
annual_leave_sheet_name = '2021'

flexible = r'调休表.xlsx'
flexible_sheet_name = '2021'


def read_table(tb_name, employee_number, lang='zh') -> str:
    """
    读表格, 返回一个 html 的字符串
    :param tb_name: {"年假表.xlsx", "调休表.xlsx"}
    :param employee_number: 员工号
    :param lang: {"zh", "en"} 分别表示 中文 英文
    :return: html
    """
    if '年假表.xlsx' in tb_name:
        sheet_name = annual_leave_sheet_name
    else:
        sheet_name = flexible_sheet_name
    df = pd.read_excel(tb_name,
                       sheet_name=sheet_name,
                       header=[0, 1, 2])
    df = df.loc[:, (1, IndexSlice[:], IndexSlice[:])].droplevel(0, axis=1)
    if lang == 'zh':
        df = df.droplevel(0, axis=1)
    else:
        df = df.droplevel(1, axis=1)

    col0_name = df.columns[0]
    df = df.dropna(subset=[col0_name])
    df[col0_name] = df[col0_name].astype(int)
    df = df[df[col0_name] == employee_number]
    html = None
    if len(df) > 0:
        html = df.to_html(index=False, classes=['table', 'table-bordered'])
    return html


@app.route('/')
def hello_world():
    employee_number = request.args.get('employee_number')
    lang = request.args.get('lang', 'zh')

    try:
        employee_number = int(employee_number)
    except:
        pass

    html_annual_leave = read_table(
        tb_name=annual_leave,
        employee_number=employee_number,
        lang=lang
    )
    html_flexible = read_table(
        tb_name=flexible,
        employee_number=employee_number,
        lang=lang
    )

    return render_template(
        'index.html',
        update_time=update_time,
        html_annual_leave=html_annual_leave,
        html_flexible=html_flexible,
        employee_number=employee_number,
        lang=lang
    )


if __name__ == '__main__':
    app.run()
